package vn.student.database;

import static vn.student.database.DBComman.*;
import vn.student.obj.CaNhan;
import static vn.student.util.CusorUtil.*;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.text.format.Time;
import android.util.Log;

public class SQLDataHelper {
    private Context mContext;
    private String queryCreateTableCanhan = "Create table if not exists "
	    + TABLE_CANHAN + " (" + ID
	    + " INTEGER PRIMARY KEY AUTOINCREMENT, " + TITLE + " TEXT, " + TIME
	    + " TEXT, " + CONTENT + " TEXT, " + AUDIO + " TEXT, " + ENABLE
	    + " INTEGER);";
    private String queryCreateTableThongBao = "Create table if not exists "
	    + TABLE_THONGBAO + " (" + ID
	    + " INTEGER PRIMARY KEY AUTOINCREMENT, " + IDMSG + " TEXT, "
	    + TITLE + " TEXT, " + SENTTIME + " TEXT, " + RECEIVEDTIME
	    + " TEXT, " + CONTENT + " TEXT ," + IDSHARED + " INTEGER);";

    SQLiteDatabase mDb;
    SqlOpenHelper sqlHelper;

    class SqlOpenHelper extends SQLiteOpenHelper {

	public SqlOpenHelper(Context context, String name,
		CursorFactory factory, int version) {
	    super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	    Log.d("Thuc hien", "Tao csdl");
	    db.execSQL(queryCreateTableThongBao);
	    db.execSQL(queryCreateTableCanhan);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    db.execSQL("Drop table if exists " + TABLE_CANHAN
		    + ";Drop table if exists " + TABLE_THONGBAO);

	    onCreate(db);
	}
    }

    public SQLDataHelper(Context context) {
	mContext = context;
    }

    public SQLDataHelper open() throws SQLException {
	// Log.d("open connect", queryCreateDB);
	sqlHelper = new SqlOpenHelper(mContext, DATABASENAME, null, 1);
	mDb = sqlHelper.getWritableDatabase();
	return this;
    }

    public void close() {
	if (mDb.isOpen())
	    mDb.close();
    }

    public boolean isOpenConnect() {
	return mDb.isOpen();
    }

    // / canhan(_id,title,time,content,audio,enable)
    // /thongbao(_id,idmsg,title,senttime,teceivedtime,content,idshared)
    public long insertCanhan(String title, String time, String content,
	    String audio, int enable) {
	ContentValues contentValues = new ContentValues();
	contentValues.put(TIME, time);
	contentValues.put(TITLE, title);
	contentValues.put(CONTENT, content);
	contentValues.put(AUDIO, audio);
	contentValues.put(ENABLE, enable);

	return mDb.insert(TABLE_CANHAN, null, contentValues);
    }

    public long updateCanhan(int id, String title, String time, String content,
	    String audio, int enable) {
	ContentValues contentValues = new ContentValues();
	contentValues.put(TIME, time);
	contentValues.put(TITLE, title);
	contentValues.put(CONTENT, content);
	contentValues.put(AUDIO, audio);
	contentValues.put(ENABLE, enable);

	return mDb.update(TABLE_CANHAN, contentValues, ID + " = " + id, null);
    }

    public long deleteCanhan(String[] id) {
	return mDb.delete(TABLE_CANHAN, ID, id);
    }

    public Cursor getALLCanhan() {
	return mDb.query(TABLE_CANHAN, new String[] { ID, TITLE, TIME,
		"substr(" + CONTENT + ",0,30)||'...' as " + CONTENT }, null,
		null, null, null, TIME + " DESC");
    }

    public CaNhan getCaNhan(int id) {
	CaNhan caNhan = new CaNhan();

	Cursor c = mDb.query(TABLE_CANHAN, null, ID + " = " + id, null, null,
		null, null);
	if (c.moveToFirst()) {
	    caNhan.setId(getCusorStr(c, ID));
	    caNhan.setTitle(getCusorStr(c, TITLE));
	    caNhan.setTime(getCusorStr(c, TIME));
	    caNhan.setContent(getCusorStr(c, CONTENT));
	    caNhan.setEnable(getCusorInt(c, ENABLE));
	    caNhan.setAudio(getCusorStr(c, AUDIO));

	} else {
	    caNhan = null;
	}
	c.close();
	return caNhan;

    }

    // / canhan(_id,title,time,content,audio,enable)
    // /thongbao(_id,idmsg,title,senttime,receivedtime,content,idshared)
    public long insertThongBao(String idmsg, String title, String senttime,
	    String receivedtime, String content, int idshared) {
	ContentValues contentValues = new ContentValues();
	contentValues.put(IDMSG, idmsg);
	contentValues.put(TITLE, title);
	contentValues.put(CONTENT, content);
	contentValues.put(SENTTIME, senttime);
	contentValues.put(RECEIVEDTIME, receivedtime);
	contentValues.put(IDSHARED, idshared);
	//open();
	long values=mDb.insert(TABLE_THONGBAO, null, contentValues);
	//close();
	return values;
    }

    public long updateThongBao(String idmsg, String title,
	    String senttime, String receivedtime, String content, int idshared) {
	ContentValues contentValues = new ContentValues();
	contentValues.put(IDMSG, idmsg);
	contentValues.put(TITLE, title);
	contentValues.put(CONTENT, content);
	contentValues.put(SENTTIME, senttime);
	contentValues.put(RECEIVEDTIME, receivedtime);
	contentValues.put(IDSHARED, idshared);

	return mDb.update(TABLE_THONGBAO, contentValues,null, null);
    }

    public long deleteThongBao(String[] id) {
	return mDb.delete(TABLE_THONGBAO, ID, id);
    }

    public Cursor getALLThongBao() {
	return mDb.query(TABLE_THONGBAO, null, null, null, null, null,
		RECEIVEDTIME + " DESC");
    }

}
